var express = require('express');
var router = express.Router();


var db = require('../model/index')

/* GET users listing. */
router.get('/', (req, res, next) => {
  let page = (req.query.page - 1) * 10
  let page_max = req.query.page * 10
  let mysql = `SELECT * FROM students ORDER BY name DESC LIMIT ?,?`
  db(mysql,[page,page_max],(err,data)=> {
    if (err) {
      return res.json({
        state:500,
        message: '数据库操作失败，请稍后重试',
      })
    }

    res.json({
      state: 200,
      message:'数据库操作成功',
      data:data
    })
  })
});

/* 增 */
router.post('/add', (req, res, next) => {
  let name = req.query.name;
  let sex = req.query.sex;
  let date = req.query.date;
  let address = req.query.address;
  let major = req.query.major;
  let phone = req.query.phone
  let mysql = `INSERT INTO students(name,sex,date,address,major,phone,password,studentNumber,state) VALUES(?,?,?,?,?,?,'123456','18101020319','否')`
  db(mysql,[name,sex,date,address,major,phone],(err,data)=> {
    
    if (err) {
      return res.json({
        state:500,
        message: '数据库操作失败，请稍后重试',
      })
    }

    res.json({
      state: 200,
      message:'添加操作成功'
    })
  })
});

/* 获取页码. */
router.get('/pagetotal', (req, res, next) => {
  
  let mysql = `SELECT * FROM students;`
  db(mysql,[],(err,data)=> {
    if (err) {
      return res.json({
        state:500,
        message: '数据库操作失败，请稍后重试',
      })
    }

    res.json({
      state: 200,
      message:'数据库操作成功',
      data:data
    })
  })
});

/* 改 */
router.put('/change', (req, res, next) => {
  let name = req.query.name;
  let sex = req.query.sex;
  let date = req.query.date;
  let address = req.query.address;
  let major = req.query.major;
  let state = req.query.state;
  let id = Number(req.query.id)
  let studentNumber = req.query.studentNumber;
  let mysql = `UPDATE students SET name=?,sex=? ,major=? ,date=? ,address=?,state=?,studentNumber=? WHERE id=?`
  db(mysql,[name,sex,major,date,address,state,studentNumber,id],(err,data)=> {
    
    if (err) {
      return res.json({
        state:500,
        message: '数据库操作失败，请稍后重试',
      })
    }

    res.json({
      state: 200,
      message:'修改成功'
    })
  })
});

/* 获取页码. */
router.get('/message', (req, res, next) => {

  let mysql = `SELECT * FROM books_message WHERE book_number=?`
  db(mysql, [req.query.book_number], (err, data) => {
    if (err) {
      return res.json({
        state: 500,
        message: '数据库操作失败，请稍后重试',
      })
    }

    res.json({
      state: 200,
      message: '数据库操作成功',
      data: data
    })
  })
});

// 重置密码
router.put('/repassword', (req, res, next) => {

  let id = req.query.id
  let mysql = `UPDATE students SET password="123456" WHERE id=?`
  db(mysql,[id],(err,data)=> {
    
    if (err) {
      return res.json({
        state:500,
        message: '数据库操作失败，请稍后重试',
      })
    }

    res.json({
      state: 200,
      message:'修改成功,密码重置为123456！'
    })
  })
});


/* GET users listing. */
router.delete('/delete', (req, res, next) => {
  let mysql = `DELETE FROM students WHERE id=?`
  db(mysql,[req.query.id],(err,data)=> {
    console.log(mysql + req.query.id);
    if (err) {
      return res.json({
        state:500,
        message: '数据库操作失败，请稍后重试',
      })
    }

    res.json({
      state: 200,
      message:'数据库删除成功'
    })
  })
});

module.exports = router;